<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2019 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\report\controller;

use app\admin\service\QueueService;
use app\report\queue\CountQueue;
use library\Controller;
use service\ExcelService;
use service\ZipService;
use think\Db;

/**
 * Excel报告
 * Class Config
 * @package app\wechat\controller
 */
class Index extends Controller
{

    /**
     * 生成报告
     * @auth true
     * @menu true
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
     * @throws \think\Exception
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function create()
    {
        if ($this->request->isGet()) {
            $this->title = '转化报告';

            if ($this->request->get('action') === 'export') {
                $type = $this->request->get('type');
                switch ($type) {
                    case 'ad':
                        $title = '广告报告';
                        $data = $this->eptAd();
                        break;

                    case 'trade':
                        $title = '销售报告';
                        $data = $this->eptTrade();
                        break;

                    case 'storage':
                        $title = '库龄报告';
//                        echo '<script>alert("报告未好");history.go(-1)</script>';
//                        exit;
                        $data = $this->eptStorage();
                        break;

                    case 'refund':
                        $title = '退货报告';
                        $data = $this->eptRefund();
                        break;

                    case 'order':
                        $title = '移除订单';
                        $data = $this->eptOrder();
                        break;
                }


                ExcelService::export($data['field'], $data['list'], $title . '_' . date('Ymd_His'));
                exit;
            }

            $this->fetch();
        } else {
            $input = [
                'ad'      => $this->request->post('ad'),
                'trade'   => $this->request->post('trade'),
                'storage' => $this->request->post('storage'),
                'refund'  => $this->request->post('refund'),
                'order'   => $this->request->post('order'),
            ];

            foreach ($input as $type => $file) {
                if (!empty($file)) {
                    $queueData = ['zipFile' => $file, 'type' => $type];
                    QueueService::add("[{$type}]转化报表", CountQueue::URI, 1, $queueData, 0, "转化报表");
                }
            }
            $this->success('创建转化报表成功！');
        }
    }

    /**
     * 广告报告
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptAd()
    {
        $list = Db::name('ReportAdUs')->field('store,msku,SUM(money) total_money')->group('msku')->order('id asc')->select();
        $fields = [
            'store'       => 'store',
            'msku'        => 'MSKU',
            'total_money' => '金额',
        ];

        return ['list' => $list, 'field' => $fields];
    }

    /**
     * 销售报告
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptTrade()
    {
        $field = "store,sku,"
            . "SUM(IF(type='Order',quantity,0)) AS quantity,"
            . "SUM(IF(type='Refund' and product_sales != 0,quantity,0)) AS quantity_refund,"
            . "SUM(IF(type='Adjustment' and description != 'FBA Inventory Reimbursement - Customer Return' and description!='FBA Inventory Reimbursement - Damaged:Warehouse',quantity,0)) AS quantity_fba_lose,"
            . "SUM(shipping_credits) AS shipping_credits,"
            . "SUM(product_sales) AS product_sales,"
            . "SUM(promotional_rebates) AS promotional_rebates,"
            . "SUM(sales_tax_collected) AS sales_tax_collected,"
            . "SUM(marketplace_facilitator_tax) AS marketplace_facilitator_tax,"
            . "SUM(selling_fees) AS selling_fees,"
            . "SUM(fba_fees) AS fba_fees,"
            . "SUM(other_transaction_fees) AS other_transaction_fees,"
            . "SUM(other) AS order_other,"
            . "SUM(IF(type='Service Fee',other,0)) AS service_fee_other,"
            . "SUM(IF(type='Refund',other,0)) AS refund_other,"
            . "SUM(IF(type='Adjustment',other,0)) AS adjustment_other,"
            . "SUM(IF(type='Lightning Deal Fee',other,0)) AS lightning_deal_fee_other,"
            . "SUM(IF(type='Debt',other,0)) AS debt_other,"
            . "SUM(IF(type='Chargeback Refund',other,0)) AS chargeback_Refund_other,"
            . "SUM(IF(type='FBA Inventory Fee',other,0)) AS fba_Inventory_Fee_other,"
            . "SUM(gift_wrap_credits) AS gift_wrap_credits_fees,"
            . "SUM(IF(type='Transfer',total,0)) AS tixian,"
            . "SUM(IF(type!='Transfer',total,0)) AS xiaoshoue";
        $list = Db::name('SalesUs')->field($field)->group('sku')->order('sku asc')->select();

        $fields = [
            'store'                       => 'store',
            'sku'                         => 'sku',
            'xiaoshoue'                   => '销售净额',
            'quantity'                    => '销售量',
            'quantity_refund'             => '退款量',
            'quantity_fba_lose'           => 'FBA丢失量',
            'shipping_credits'            => '运费',
            'product_sales'               => '商品价格',
            'promotional_rebates'         => '促销返点',
            'sales_tax_collected'         => '销售税',
            'marketplace_facilitator_tax' => '平台服务税',
            'selling_fees'                => '佣金',
            'fba_fees'                    => '亚马逊物流基础服务费',
            'other_transaction_fees'      => '广告费用',
            'order_other'                 => '其他_订单',
            'service_fee_other'           => '其他：服务费',
            'refund_other'                => '其他：退货',
            'adjustment_other'            => '其他：清算',
            'lightning_deal_fee_other'    => '其他：闪电交易费',
            'debt_other'                  => '其他：Debt',
            'chargeback_Refund_other'     => '其他：退款',
            'fba_Inventory_Fee_other'     => '其他：FBA 库存费',
            'gift_wrap_credits_fees'      => '包装费',
            'tixian'                      => '其他:提款',
        ];

        return ['list' => $list, 'field' => $fields];
    }

    /**
     * 库龄报告
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptStorage()
    {
        $list = Db::name('ReportStorageUs')->select();
        $fields = [
            'store'                   => 'store',
            'msku'                    => 'MSKU',
            'fnsku'                   => 'fnsku',
            'asin'                    => 'asin',
            'inv_age_0_to_90_days'    => 'inv-age-0-to-90-days',
            'inv_age_91_to_180_days'  => 'inv-age-91-to-180-days',
            'inv_age_181_to_270_days' => 'inv-age-181-to-270-days',
            'inv_age_271_to_365_days' => 'inv-age-271-to-365-days',
            'inv_age_365_plus_days'   => 'inv-age-365-plus-days',
        ];

        return ['list' => $list, 'field' => $fields];
    }

    /**
     * 退货报告
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptRefund()
    {
        $field = 'store,msku,'
            . 'SUM(IF(status="Unit returned to inventory" or status="Repackaged Successfully",quantity,0)) return_quantity,'
            . 'SUM(IF(status="Reimbursed",quantity,0)) reimbursed_quantity';
        $list = Db::name('ReportRefundUs')->field($field)->group('msku')->order('id asc')->select();
        $fields = [
            'store'               => 'store',
            'msku'                => 'msku',
            'return_quantity'     => '收到退货数量',
            'reimbursed_quantity' => '赔付数量',
        ];

        return ['list' => $list, 'field' => $fields];
    }

    /**
     * 移除订单
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptOrder()
    {
        $list = Db::name('ReportOrderUs')->order('id asc')->select();
        $fields = [
            'store'               => 'store',
            'order_type'          => 'order_type',
            'order_status'        => 'order-status',
            'msku'                => 'MSKU',
            'fnsku'               => 'fnsku',
            'disposition'         => 'disposition',
            'requested_quantity'  => 'requested-quantity',
            'cancelled_quantity'  => 'cancelled-quantity',
            'disposed_quantity'   => 'disposed-quantity',
            'shipped_quantity'    => 'shipped-quantity',
            'in_process_quantity' => 'in-process-quantity',
        ];

        return ['list' => $list, 'field' => $fields];
    }

    /**
     * 移除订单汇总
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    protected function eptOrderCount()
    {
        $field = 'store,msku,'
            . 'SUM(IF(status="Unit returned to inventory" or status="Repackaged Successfully",quantity,0)) return_quantity,'
            . 'SUM(IF(status="Reimbursed",quantity,0)) reimbursed_quantity';
        $list = Db::name('ReportRefundUs')->field($field)->group('msku')->order('id asc')->select();
        $fields = [
            'store'               => 'store',
            'msku'                => 'msku',
            'return_quantity'     => '收到退货数量',
            'reimbursed_quantity' => '赔付数量',
        ];

        return ['list' => $list, 'field' => $fields];
    }

}
